﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CarLoanSystem.Entity;
using System.Data.SqlClient;
using System.Data;

namespace CarLoanSystem.DAL
{
    public class LitigationInfoDAL : DBBase
    {
        public static LitigationInfoItem LoadLitigation(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT [LITIGATION_ID]
                                                          ,[LOAN_ID]
                                                          ,[EMPLOYEE_ID]
                                                          ,[LITIGATION_STATUS]
                                                      FROM [CAR_LOAN].[dbo].[LITIGATION_INFO]
                                                    where [LOAN_ID] = @LOAN_ID ", DBBase.con);
                command.Parameters.AddWithValue("@LOAN_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    LitigationInfoItem item = new LitigationInfoItem();
                    item.LITIGATION_ID = Convert.ToInt32(dt.Rows[0]["LITIGATION_ID"].ToString());
                    item.LOAN_ID = Convert.ToInt32(dt.Rows[0]["LOAN_ID"].ToString());
                    item.EMPLOYEE_ID = Convert.ToInt32(dt.Rows[0]["EMPLOYEE_ID"].ToString());
                    item.LITIGATION_STATUS = dt.Rows[0]["LITIGATION_STATUS"].ToString();
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        // โหลดข้อมูลการค้างชำระ
        public static DataTable LoanUnPaid(string date)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"select * from (
                            SELECT dbo.LOAN.LOAN_ID , COUNT(dbo.LOAN.LOAN_ID) as UN_PAID_QTY
                            FROM dbo.CUSTOMER INNER JOIN
                            dbo.LOAN ON dbo.CUSTOMER.CUSTOMER_ID = dbo.LOAN.CUSTOMER_ID INNER JOIN
                            dbo.PAYMENTS ON dbo.LOAN.LOAN_ID = dbo.PAYMENTS.LOAN_ID
                            where dbo.PAYMENTS.PAYMENTS_DUE_DATE < @PAYMENTS_DUE_DATE and dbo.LOAN.LOAN_STATUS = 'I'
                             and dbo.LOAN.DEBT_MONTH_REST > 0 and dbo.PAYMENTS.PAYMENTS_STATUS = 'A' and dbo.LOAN.DEBT_STATUS = ''
                            Group by dbo.LOAN.LOAN_ID ) tmp where UN_PAID_QTY >= 2", DBBase.con);
                command.Parameters.AddWithValue("@PAYMENTS_DUE_DATE", date);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoanAllLitigation(string date, string loadId, string name, string lastName)
        {
            try
            {
                DBBase.ConncetDatabase();

                string sqlQuery = @"select CUS.LOAN_ID,CUS.CUSTOMER_NAME,CUS.CUSTOMER_LASTNAME,DEBT2.UN_PAID,DEBT2.UN_PAID_QTY from (
            select dbo.LOAN.LOAN_ID,dbo.CUSTOMER.CUSTOMER_NAME , dbo.CUSTOMER.CUSTOMER_LASTNAME ,
            dbo.SCHEDULE.EMPLOYEE_ID from dbo.CUSTOMER
            INNER JOIN dbo.LOAN ON dbo.CUSTOMER.CUSTOMER_ID = dbo.LOAN.CUSTOMER_ID INNER JOIN dbo.SCHEDULE
             ON dbo.LOAN.LOAN_ID = dbo.SCHEDULE.LOAN_ID where dbo.SCHEDULE.SCHEDULE_JOB = 'D' Group by dbo.LOAN.LOAN_ID 
            , dbo.CUSTOMER.CUSTOMER_NAME , dbo.CUSTOMER.CUSTOMER_LASTNAME , dbo.SCHEDULE.EMPLOYEE_ID) CUS INNER JOIN 
            (select * from (SELECT  dbo.LOAN.LOAN_ID, SUM(dbo.LOAN.PAY_MONTH) AS UN_PAID , COUNT(dbo.LOAN.LOAN_ID) as UN_PAID_QTY
            FROM dbo.CUSTOMER INNER JOIN
            dbo.LOAN ON dbo.CUSTOMER.CUSTOMER_ID = dbo.LOAN.CUSTOMER_ID INNER JOIN
            dbo.PAYMENTS ON dbo.LOAN.LOAN_ID = dbo.PAYMENTS.LOAN_ID
            where  dbo.PAYMENTS.PAYMENTS_DUE_DATE < @PAYMENTS_DUE_DATE and dbo.LOAN.LOAN_STATUS = 'I'
            and dbo.LOAN.DEBT_MONTH_REST > 0 and dbo.PAYMENTS.PAYMENTS_STATUS = 'A' and dbo.LOAN.DEBT_STATUS = 'B'
            Group by dbo.LOAN.LOAN_ID ) DEBT ) DEBT2 ON CUS.LOAN_ID = DEBT2.LOAN_ID
            where DEBT2.UN_PAID_QTY >= 1";
                SqlDataReader dr;

                if (loadId != "")
                {
                    sqlQuery += " and CUS.LOAN_ID = " + loadId;
                }
                if (name != "")
                {
                    sqlQuery += " and CUS.CUSTOMER_NAME like '%" + name + "%'";
                }
                if (lastName != "")
                {
                    sqlQuery += " and CUS.CUSTOMER_LASTNAME like '%" + lastName + "%'";
                }

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                command.Parameters.AddWithValue("@PAYMENTS_DUE_DATE", date);

                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return dt;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static void Insert(LitigationInfoItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[LITIGATION_INFO]
                                                       ([LOAN_ID]
                                                       ,[EMPLOYEE_ID]
                                                       ,[LITIGATION_STATUS])
                                                 VALUES
                                                      ( @LOAN_ID
                                                       ,@EMPLOYEE_ID
                                                       ,@LITIGATION_STATUS)", DBBase.con);

                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                command.Parameters.AddWithValue("@EMPLOYEE_ID", item.EMPLOYEE_ID);
                command.Parameters.AddWithValue("@LITIGATION_STATUS", item.LITIGATION_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static void UpdateLitigationStatus(LitigationInfoItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[LITIGATION_INFO]
                                               SET [LITIGATION_STATUS] = @LITIGATION_STATUS
                                             WHERE ([LITIGATION_ID] = @LITIGATION_ID)", DBBase.con);

                command.Parameters.AddWithValue("@LITIGATION_ID", item.LITIGATION_ID);
                command.Parameters.AddWithValue("@LITIGATION_STATUS", item.LITIGATION_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }
    }
}
